1. Field of the Invention
The present invention relates generally to information processing environments and, more particularly, to a system and methodology for automatically tuning optimization of database queries.
2. Description of the Background Art
Computers are very powerful tools for storing and providing access to vast amounts of information. Computer databases are a common mechanism for storing information on computer systems while providing easy access to users. A typical database is an organized collection of related information stored as “records” having “fields” of information. As an example, a database of employees may have a record for each employee where each record contains fields designating specifics about the employee, such as name, home address, salary, and the like.
Between the actual physical database itself (i.e., the data actually stored on a storage device) and the users of the system, a database management system or DBMS is typically provided as a software cushion or layer. In essence, the DBMS shields the database user from knowing or even caring about the underlying hardware-level details. Typically, all requests from users for access to the data are processed by the DBMS. For example, information may be added or removed from data files, information retrieved from or updated in such files, and so forth, all without user knowledge of the underlying system implementation. In this manner, the DBMS provides users with a conceptual view of the database that is removed from the hardware level. The general construction and operation of database management systems is well known in the art. See e.g., Date, C., “An Introduction to Database Systems, Seventh Edition”, Part I (especially Chapters 1-4), Addison Wesley, 2000.
All database vendors routinely update their products and issue new releases to customers. With each new release, customers of a given database management system can expect significant improvements over prior versions. However, in specific instances the performance may regress (i.e., worsen) over prior versions, especially due to “runaway queries” (i.e., queries for which the database optimizer has selected an inefficient query execution plan). Although a particular customer's motivation for upgrading may be to obtain a new feature (rather than performance improvement), customers also generally expect that performance of the new version of the DBMS should be no worse than the prior version (i.e., no performance regression). For example, customers would be dissuaded from upgrading if significant number of queries were executed less efficiently by the new version when compared to the prior version (i.e., if optimizer plan change regressions occurred). Moreover, after upgrade, as the database state changes, it would be possible for query plan changes to occur and to introduce similar performance regressions.
What is needed is a database system (operating with minimum DBA intervention) that provides methods capable of avoiding runaway queries occurring in new releases (i.e., namely, with an optimizer delivered with a new release) as compared to prior releases. In such a system, the plan chosen by the optimizer should be at least as good as the prior-version optimizer generated plan. Significantly, when the server recognizes a bad (inefficient) plan, the optimizer should know to avoid it at the next time of compilation. The present invention fulfills this and other needs.